package com.cheeven.cwliu.citation.analysis;

import au.com.bytecode.opencsv.CSVReader;
import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileReader;
import java.io.FileWriter;
import java.io.FilenameFilter;
import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Properties;

public class ImportDB {

	public static void main(String[] args) throws FileNotFoundException, IOException, ClassNotFoundException, SQLException {

		Properties config = new Properties();
		config.load(new FileReader("configuration.txt"));

		File sourceFolder = new File(config.getProperty("save.folder"));
		File[] subFolders = sourceFolder.listFiles(new FilenameFilter() {
			@Override
			public boolean accept(File dir, String name) {
				return !(name.startsWith(".") || name.startsWith("_"));
			}
		});

		FileWriter exceptionOut = new FileWriter(config.getProperty("import.exception.log"));

		Connection crDB = DBUtil.connect();
		PreparedStatement psInsertData = crDB.prepareStatement(DBUtil.INSERT_RAW_DATA_SQL);
		PreparedStatement psFindOldData = crDB.prepareStatement(DBUtil.FIND_OLD_DATA_IN_RAW_DATA_TABLE_SQL);
		PreparedStatement psDeleteOldData = crDB.prepareStatement(DBUtil.DELETE_OLD_DATA_IN_RAW_DATA_TABLE_SQL);

		for (File folder : subFolders) {
			System.out.println(folder);

			File[] csvFiles = folder.listFiles(new FilenameFilter() {
				@Override
				public boolean accept(File dir, String name) {
					return name.endsWith(".csv") && !name.startsWith("_");
				}
			});

			int exceptionFileCount = 0;
			for (File csvFile : csvFiles) {
				System.out.println(csvFile);
				String[] ss = csvFile.getName().split("[-\\.]");
				String docType = ss[1];
				int start = Integer.parseInt(ss[2]);
				int end = Integer.parseInt(ss[3]);
				System.out.println("\tDocument Type:\t" + docType);
				System.out.println("\tStart Row Number:\t" + start);
				System.out.println("\tEnd Row Number:\t" + end);

				psFindOldData.clearParameters();
				psFindOldData.setString(1, csvFile.getPath());
				ResultSet rs = psFindOldData.executeQuery();
				if (rs.next()) {
					int oldDataCount = rs.getInt(1);
					rs.close();
					if (oldDataCount > 0) {
						System.out.printf("\tFound %d old data\n", oldDataCount);
						psDeleteOldData.clearParameters();
						psDeleteOldData.setString(1, csvFile.getPath());
						int deletedOldDataCount = psDeleteOldData.executeUpdate();
						System.out.printf("\tDeleted %d old data\n", deletedOldDataCount);
					}
				}

				CSVReader csv = new CSVReader(new FileReader(csvFile));
				csv.readNext(); // the header row, not used

				int insertedNewDataCount = 0;
				int exceptionDataCount = 0;
				for (int i = 1; i <= end - start + 1; i++) {
					String[] lineData = csv.readNext(); // read data

					if (lineData == null) {
						exceptionOut.write("\"" + csvFile + "\", \"" + i + "\", \"not enough data in the file\"\n");
						exceptionDataCount++;
						break;
					}

					if (lineData.length != DBUtil.RAW_DATA_COLUMN_COUNT) {
						exceptionOut.write("\"" + csvFile + "\", \"" + i + "\", \"wrong number of columns: " + lineData.length + " - should be " + DBUtil.RAW_DATA_COLUMN_COUNT + "\n");
						exceptionDataCount++;
					}

					psInsertData.clearParameters();
					try {
						psInsertData.setString(1, csvFile.getPath());
						psInsertData.setInt(2, i);
						for (int col = 0; col < DBUtil.RAW_DATA_COLUMN_COUNT; col++) {
							psInsertData.setString(col + 3, lineData[col]);
						}
						psInsertData.executeUpdate();
						insertedNewDataCount++;
					} catch (SQLException e) {
						exceptionOut.write("\"" + csvFile + "\", \"" + i + "\", \"" + e + "\"\n");
						exceptionDataCount++;
					}

				} // end of for (int i = 0; i <= end - start; i++)

				System.out.printf("\tInserted %d new data\n", insertedNewDataCount);
				System.out.printf("\tUnimported data: %d\n", exceptionDataCount);

				if (exceptionDataCount == 0) {
					File newCsvFile = new File(csvFile.getParentFile(), "_" + csvFile.getName());
					csvFile.renameTo(newCsvFile);
					System.out.printf("\tRenaming CSV file from %s to %s\n", csvFile.getPath(), newCsvFile.getPath());
				} else {
					exceptionFileCount++;
				}
			}

			if (exceptionFileCount == 0) {
				File newFolder = new File(folder.getParentFile(), "_" + folder.getName());
				folder.renameTo(new File(folder.getParentFile(), "_" + folder.getName()));
				System.out.printf("\tRenaming folder from %s to %s\n", folder.getPath(), newFolder.getPath());
			}


		} // end of for (File folder : subFolders)
		exceptionOut.flush();
		exceptionOut.close();

	}
}
